#include "mysqlserver.h"
#include <QDebug>


//#include "predata/predata.h"
//#include "fuzzy/fuzzy.h"

#include "adddate.h"

//时间转换
QDateTime get_cot_dateTime(QString str){
    QString t = str;

    QString t1;
    if(t.mid(3,3)=="JAN"){
        t1 = t.replace(3,3,"01").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="FEB"){
        t1 = t.replace(3,3,"02").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="MAR"){
        t1 = t.replace(3,3,"03").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="APR"){
        t1 = t.replace(3,3,"04").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="MAY"){
        t1 = t.replace(3,3,"05").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="JUN"){
        t1 = t.replace(3,3,"06").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="JUL"){
        t1 = t.replace(3,3,"07").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="AUG"){
        t1 = t.replace(3,3,"08").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="SEP"){
        t1 = t.replace(3,3,"09").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="OCT"){
        t1 = t.replace(3,3,"10").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="NOV"){
        t1 = t.replace(3,3,"11").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="DEC"){
        t1 = t.replace(3,3,"12").insert(6,"20").remove(19,2);
    }

    QDateTime time = QDateTime::fromString(t1, "dd-MM-yyyy hh:mm:ss");
    return time;
}
QDate get_cot_date(QString str){
    QString t = str;

    QString t1;
    if(t.mid(3,3)=="JAN"){
        t1 = t.replace(3,3,"01").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="FEB"){
        t1 = t.replace(3,3,"02").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="MAR"){
        t1 = t.replace(3,3,"03").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="APR"){
        t1 = t.replace(3,3,"04").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="MAY"){
        t1 = t.replace(3,3,"05").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="JUN"){
        t1 = t.replace(3,3,"06").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="JUL"){
        t1 = t.replace(3,3,"07").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="AUG"){
        t1 = t.replace(3,3,"08").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="SEP"){
        t1 = t.replace(3,3,"09").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="OCT"){
        t1 = t.replace(3,3,"10").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="NOV"){
        t1 = t.replace(3,3,"11").insert(6,"20").remove(19,2);
    }
    else if (t.mid(3,3)=="DEC"){
        t1 = t.replace(3,3,"12").insert(6,"20").remove(19,2);
    }

    QDateTime time = QDateTime::fromString(t1, "dd-MM-yyyy hh:mm:ss");
    return time.date();
}
QString get_cot_date_month(int month){

    if(month==1){
        return "JAN";
    }
    else if (month==2){
        return "FEB";
    }
    else if (month==3){
        return "MAR";
    }
    else if (month==4){
        return "APR";
    }
    else if (month==5){
        return "MAY";
    }
    else if (month==6){
        return "JUN";
    }
    else if (month==7){
        return "JUL";
    }
    else if (month==8){
        return "AUG";
    }
    else if (month==9){
        return "SEP";
    }
    else if (month==10){
        return "OCT";
    }
    else if (month==11){
        return "NOV";
    }
    else if (month==12){
        return "DEC";
    }
}

/**
 * @brief MysqlServer::mdeal_with
 * @param mdt
 * @description: 时间排序和处理算法，主要处理一段时间平均一天温度曲线排序
 */
void MysqlServer::mdeal_with(QList<datas_time> *mdt){
//    qDebug()<<mdt->count();
    if(mdt->count()==0)
        return;


    //排序前输出
//    qDebug()<<"排序前输出";
//    for(int a=0;a<mdt->count();a++){
//        qDebug()<<"温度"<<mdt->at(a).temp<<"时间"<<mdt->at(a).time;
//    }
//    qDebug()<<"数据长度"<<mdt->count();

    //时间排序整理

    for(int a=0;a<mdt->count();a++){
        for(int b=a;b<mdt->count();b++){
            if(mdt->at(a).time>mdt->at(b).time){
                mdt->swap(a,b);
            }
        }
    }

    //排序后输出
//    qDebug()<<"排序后输出";
//    for(int a=0;a<mdt->count();a++){
//        qDebug()<<"温度"<<mdt->at(a).temp<<"时间"<<mdt->at(a).time;
//    }

    //一天中的平均值
    for(int a=1;a<mdt->count();a++){
        if(mdt->at(a).time.date()==mdt->at(a-1).time.date()){
            (*mdt)[a-1].temp=(mdt->at(a).temp+mdt->at(a-1).temp)/2;
            mdt->takeAt(a);
            a--;
        }
    }

    //求平均值后输出

//    qDebug()<<"求平均值后输出";
//    for(int a=0;a<mdt->count();a++){
//        qDebug()<<"温度"<<mdt->at(a).temp<<"时间"<<mdt->at(a).time;
//    }
}

/**
 * @brief MysqlServer::pushDatas
 * @param tubeNum
 * @param forunNum
 * @param location
 * @param temp
 * @param dataTime
 */
void MysqlServer::pushDatas(QString tubeNum,//炉号
                            QString forunNum,//管号
                            QString location,//位置（tube in ,tube out ,tube cot)
                            QString temp,//温度
                            QString dataTime//时间
                            ){

    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){
        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }
    QString table;
    if(location=="tube_in")
        table="table_in";
    else
        table="table_out";

    //获取数据,同时检查是否有重复数据，如果有就跳出函数
    QString querystr="SELECT Time from schema1."+table+" where Time="+"'"+dataTime+"' AND TN='"+tubeNum+"' AND FN='"+forunNum+"'";
    QSqlQuery query;
    query.exec(querystr);

    if(query.next()){
        qDebug()<<"数据重复"<<endl;
        return;
    }

//    INSERT INTO `schema1`.`table_out` (`idtable_out`, `FN`, `TN`, `Temp`, `Time`) VALUES ('1001', '5', '12', '970', '2015-02-19 09:20:23');
    //检查已经没有重复数据后把数据插入表中
    querystr="INSERT INTO `schema1`.`"+table+"` (`FN`,`TN`,`Temp`,`Time`) VALUES ('"+forunNum+"','"+tubeNum+"','"+temp+"','"+dataTime+"')";
    query.clear();
    query.exec(querystr);
    db.close();


}

/**
 * @brief MysqlServer::MysqlServer
 * @param parent
 */
MysqlServer::MysqlServer(QObject *parent) :
    QObject(parent)
{

    //初始化各个管的温度
    for(int a=0;a<48;a++){
        my_ethlene_datas.tube_in_temps[a]=0;
    }
    for(int a=0;a<48;a++){
        my_ethlene_datas.tube_out_temps[a]=0;
    }
    for(int a=0;a<48;a++){
        my_ethlene_datas.cot_temp[a]=0;
    }

    //初始化警戒温度
    init_aler_temps();
}


/**
 * @brief MysqlServer::compare_datas
 * @param forunceNum
 * @param tubeNum
 * @param from_DateTime
 * @param to_DateTime
 * @description： 管管比较
 */
QJsonArray MysqlServer::compare_datas(int forunceNum,int tubeNum,QDateTime from_DateTime, QDateTime to_DateTime){

    //管管比较后存放的数据
    QList<datas_time> tube_in_compare_datas;
    QList<datas_time> tube_out_compare_datas;
    QList<datas_time> tube_cot_compare_datas;


    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){

        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }

    //数据库查询对象
    QSqlQuery query;
    //过滤入管的数据语句
    QString sqlstr1="select * from table_in where FN="+QString::number (forunceNum)+
            " and TN='"+QString::number (tubeNum)+"'"+
            " and Time>='"+from_DateTime.date ().toString ("yyyy-MM-dd")+"'"+
            " and Time<='"+to_DateTime.date ().toString ("yyyy-MM-dd")+"'";
    query.exec(sqlstr1);
    //获取入管数据
    while(query.next()){
        datas_time data;
        data.time=query.value("Time").toDateTime();
        data.temp=query.value("Temp").toInt();
        tube_in_compare_datas.append(data);
    }

    //清空查询条件
    query.clear();

    //过滤出管的数据语句
    QString sqlstr2="select * from table_out where FN="+QString::number (forunceNum)+
            " and TN='"+QString::number (tubeNum)+"'"+
            " and Time>='"+from_DateTime.date ().toString ("yyyy-MM-dd")+"'"+
            " and Time<='"+to_DateTime.date ().toString ("yyyy-MM-dd")+"'";

    query.exec(sqlstr2);

    //获取出管数据
    while(query.next()){
        datas_time data;
        data.time=query.value("Time").toDateTime();
        data.temp=query.value("Temp").toInt();
        tube_out_compare_datas.append(data);
    }

    //关闭数据库
    db.close();

    /**
     *由于显示温度时间单位是天，所以需要处理，求出一天平均温度
     */
    mdeal_with(&tube_in_compare_datas);
    mdeal_with(&tube_out_compare_datas);

    //封装json数据库，给qml使用
    QJsonArray jsarr;
    for(int a=0;a<tube_in_compare_datas.count ();a++){
        QJsonObject obj1;
        obj1.insert ("time",tube_in_compare_datas.at (a).time.date ().toString ("yy/M/d"));
        //temp in
        obj1.insert ("temp_in",tube_in_compare_datas.at(a).temp);
        //temp out
        obj1.insert ("temp_out",tube_out_compare_datas.at(a).temp);
        //temp cot

        jsarr.append (obj1);
    }

    //清空临时链表
    tube_in_compare_datas.clear ();
    tube_out_compare_datas.clear ();
    tube_cot_compare_datas.clear ();

    return jsarr;
}

//全部显示
//??链接乙烯厂的有点不懂
void MysqlServer::all_tube_show(int forunceNum,QDateTime from_DateTime, QDateTime to_DateTime){
    from_DateTime.setTime(QTime(0,0));
    to_DateTime.setTime(QTime(23,59,59));

//    qDebug()<<"全部显示的日期为："<<from_DateTime.toString(Qt::ISODate)<<"  到  "<<to_DateTime.toString(Qt::ISODate);

    //清空数据
    for(int i = 0 ; i <48 ; i++){
        tube_in_full_search_datas[i].clear();
        tube_out_full_search_datas[i].clear();
        tube_cot_full_search_datas[i].clear();
    }


    /**********************************查询本机的数据库**********************************/
    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){

        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }

    //获取数据
    QSqlQuery query;
    QList<QDateTime> get_outTime_forCot[48];//??这有何用


    for ( int tubeNum = 1 ; tubeNum < 49 ; tubeNum++){
        //过滤入管的数据语句
        /*qDebug()<<"查询检测"<<*/query.exec("select * from table_in");
        //数据获取
        while(query.next()){

            //判断初始值是否为空
            //??为什么不将后两个条件作为数据库查询条件
            if(query.value(4).toDateTime()>=from_DateTime && query.value(4).toDateTime()<=to_DateTime && query.value(2)==tubeNum && query.value(1)==forunceNum){

                datas_time data;
                data.time=query.value(4).toDateTime();
                data.temp=query.value(3).toInt();
//                qDebug()<<"tubeInNum : "<<tubeNum<<"time : "<<data.time<<"temp : "<<data.temp;
                tube_in_full_search_datas[tubeNum-1].append(data);

            }

        }
        /*********清空查询条件********/
        query.clear();
        /*********清空查询条件********/
    }


    for ( int tubeNum = 1 ; tubeNum < 49 ; tubeNum++){
       /* qDebug()<<"查询检测"<<*/query.exec("select * from table_out");
        //数据获取

        while(query.next()){
            //判断初始值是否为空
            if(query.value(4).toDateTime()>=from_DateTime && query.value(4).toDateTime()<=to_DateTime && query.value(2)==tubeNum && query.value(1)==forunceNum){
                datas_time data;
                data.time=query.value(4).toDateTime();
                data.temp=query.value(3).toInt();
//                qDebug()<<"tubeOutNum : "<<tubeNum<<"time : "<<data.time<<"temp : "<<data.temp;
                tube_out_full_search_datas[tubeNum-1].append(data);

                int hour = data.time.time().hour();
                int minute = data.time.time().minute();
                data.time.setTime(QTime(hour,minute));
                get_outTime_forCot[tubeNum-1].append(data.time);
            }
        }
        /*********清空查询条件********/
        query.clear();
        /*********清空查询条件********/

    }
    //关闭数据库
    db.close();
    /**********************************查询本机的数据库**********************************/

    //    //处理数据
    //    qDebug()<<"数据处理****************：“";
    //    mdeal_with(&tube_in_show_datas);
    //    mdeal_with(&tube_out_show_datas);

    /**********************************查询石油那边的数据库**********************************/
    //创建石油厂数据接口
    db=QSqlDatabase::addDatabase("QODBC");

    //连接石油厂数据库
    //
    db.setHostName("10.112.200.22");
    db.setDatabaseName("History");
    db.setPort(10014);


    if(db.open()){

        qDebug()<<"remote database is established!";
    }
    else{
        qDebug()<<"faled to connect to remote database";
        return;
    }




    //通过链接石油厂数据库加载COT温度数据


    for ( int tubeNum = 1 ; tubeNum < 49 ; tubeNum++){


        QSqlQuery query1;
        query1.setForwardOnly(true);
        if(tubeNum<10){
            QString time1String = QString::number(from_DateTime.date().day(), 10)+"-"+get_cot_date_month(from_DateTime.date().month())+"-"+QString::number(from_DateTime.date().year(), 10).right(2);
            QString time2String = QString::number(to_DateTime.date().day(), 10)+"-"+get_cot_date_month(to_DateTime.date().month())+"-"+QString::number(to_DateTime.date().year(), 10).right(2);
            query1.exec("select name,ts,value from history where name like 'TI160"+QString::number(tubeNum, 10)+"_3' and ts >='"+time1String+" 00:00:00.0' and ts<='"+time2String+" 23:59:59.0'");
            while(query1.next()){
                //                if(/*get_cot_dateTime(query1.value(1).toString()) == get_outTime_forCot[tubeNum-1].at(0) && */query1.value(0).toString().mid(4,2).toInt()==tubeNum   /*&&query1.value(0).toString().right(1).toInt()==forunceNum*/){
//                qDebug()<<query1.value(0).toString();
                qDebug()<<"get_cot_dateTime ==========================="<<get_cot_dateTime(query1.value(1).toString());
//                qDebug()<<get_outTime_forCot[tubeNum-1].at(0);

                //                        datas_time data;
                //                        data.time=get_cot_dateTime(query1.value(1).toString());
                //                        data.temp=query1.value(2).toInt();
                //                        qDebug()<<"tubeCotNum : "<<tubeNum<<"time : "<<data.time<<"temp : "<<data.temp;
                //                        tube_cot_full_search_datas[tubeNum-1].append(data);

                //                }
            }
        }
        else {
            QString time1String = QString::number(from_DateTime.date().day(), 10)+"-"+get_cot_date_month(from_DateTime.date().month())+"-"+QString::number(from_DateTime.date().year(), 10).right(2);
            QString time2String = QString::number(to_DateTime.date().day(), 10)+"-"+get_cot_date_month(to_DateTime.date().month())+"-"+QString::number(to_DateTime.date().year(), 10).right(2);
            query1.exec("select name,ts,value from history where name like 'TI16"+QString::number(tubeNum, 10)+"_3' and ts >='"+time1String+" 00:00:00.0' and ts<='"+time2String+" 23:59:59.0'");

            while(query1.next()){
                //                    if(/*get_cot_dateTime(query1.value(1).toString()) == get_outTime_forCot[get_all_tube_out_count(tubeNum)-1] &&*/query1.value(0).toString().mid(4,2).toInt()==tubeNum&&query1.value(0).toString().right(1).toInt()==forunceNum){
                //                        datas_time data;
                //                        data.time=get_cot_dateTime(query1.value(1).toString());
                //                        data.temp=query1.value(2).toInt();
                //                        qDebug()<<"tubeCotNum : "<<tubeNum<<"time : "<<data.time<<"temp : "<<data.temp;
                //                        tube_cot_full_search_datas[tubeNum-1].append(data);

                //                    }
            }





            /**********************************查询石油那边的数据库**********************************/

            //        //处理数据
            //        qDebug()<<"数据处理****************：“";
            //        mdeal_with(&tube_cot_show_datas);

        }

    }

    //断开远程数据库连接
    db.close();
}


//获取 入管,出管,ＣＯＴ 的最新温度

QJsonArray MysqlServer::access_tube_in_temp(){
    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){
        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }

    //获取数据
    QSqlQuery query;
    query.exec("SELECT * FROM schema1.table_in ORDER BY Time DESC");//降序排列

    //json 数据
    QJsonArray jsarr;
//    QJsonObject root;

    //数据获取
    while(query.next()){
        //判断初始值是否为空
        //判断程序存储值是否为空，或程序存储数据比数据库中的旧则更新
        if(my_ethlene_datas.time[query.value(2).toInt()-1].toString()==""||
                my_ethlene_datas.time[query.value(2).toInt()-1]<=((query.value(4).toDateTime()))){

            //保存最新温度数据，方便后面作时间比较
            my_ethlene_datas.tube_in_temps[query.value(2).toInt()-1]=query.value(3).toInt();
            my_ethlene_datas.time[query.value(2).toInt()-1]=query.value(4).toDateTime();

            QJsonObject jsobj;
            jsobj.insert ("temp",query.value(3).toInt());
            jsobj.insert ("time",query.value(4).toDateTime().toString (Qt::DefaultLocaleLongDate));
            jsarr.append (jsobj);
        }
    }
    //关闭数据库
    db.close();
//    root.insert ("datas",jsarr);
    return jsarr;
}
QJsonArray MysqlServer::access_tube_out_temp(){
    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){

        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }

    //json 数据
    QJsonArray jsarr;

    //获取数据
    QSqlQuery query;
    /*qDebug()<<"查询检测"<<*/query.exec("select * from table_out ");
//   /* qDebug()<<"查询检测000"<<*/query.exec("SELECT * FROM schema1.table_out WHERE Time>='"+importDate+" 00:00:00' and Time<='"+importDate+" 59:59:59'");
    qDebug()<<"查询检测AAA"<<query.exec("SELECT * FROM schema1.table_out ORDER BY Time DESC");

    //数据获取
    while(query.next()){
        //判断初始值是否为空
        if(my_ethlene_datas.time1[query.value(2).toInt()-1].toString()==""||
                my_ethlene_datas.time1[query.value(2).toInt()-1]<=(query.value(4).toDateTime())){
            //保存最新温度数据，方便后面作时间比较
            my_ethlene_datas.tube_out_temps[query.value(2).toInt()-1]=query.value(3).toInt();
            my_ethlene_datas.time1[query.value(2).toInt()-1]=query.value(4).toDateTime();

            QJsonObject jsobj;
            jsobj.insert ("temp",query.value(3).toInt());
            jsobj.insert ("time",query.value(4).toDateTime().toString (Qt::DefaultLocaleLongDate));
            jsarr.append (jsobj);
        }
    }
    //关闭数据库
    db.close();
    return jsarr;

}

//转换成单独CotServer
QJsonArray MysqlServer::access_tube_cot_temp(){
    //链接石油厂数据
    QSqlDatabase db=QSqlDatabase::addDatabase("QODBC");
    //json 数据
    QJsonArray jsarr;

    //链接石油厂数据库
    db.setHostName("10.112.200.22");
    db.setDatabaseName("History");
    db.setPort(10014);
    if(db.open()){

        qDebug()<<"remote database is established!";
    }
    else{
        qDebug()<<"faled to connect to remote database";
        return jsarr;
    }

    //通过链接石油厂数据库加载COT温度数据
    QSqlQuery query1;
    query1.setForwardOnly(true);
//    qDebug()<<query1.isForwardOnly();


    //第一种查询方法：48次查询来初始化cot数据
    for (int i=1;i<=48;i++){
        if(i<10){
            QString time1String = my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").left(2)+"-"+get_cot_date_month(my_ethlene_datas.time1[i-1].date().month())+"-"+my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").mid(6,2)+" "+my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").mid(9,2)+":"+my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").mid(12,2);
            qDebug()<<"查询检查BBB"<<query1.exec("select name,ts,value from history where name like 'TI160"+QString::number(i, 10)+"_5' and ts >='"+time1String+":00.0' and ts<='"+time1String+":59.0'");

            while(query1.next()){
                if(my_ethlene_datas.time2[i-1].toString()==""||
                        ( my_ethlene_datas.time1[i-1].date()==get_cot_date(query1.value(1).toString())&&
                          my_ethlene_datas.time1[i-1].time().hour()==get_cot_dateTime(query1.value(1).toString()).time().hour()&&
                          my_ethlene_datas.time1[i-1].time().minute()==get_cot_dateTime(query1.value(1).toString()).time().minute()
                          )){
                    my_ethlene_datas.time2[i-1]=get_cot_dateTime(query1.value(1).toString());
                    my_ethlene_datas.cot_temp[i-1]=query1.value(2).toInt();
                    QJsonObject jsobj;
                    jsobj.insert ("temp",query1.value(2).toInt());
                    jsobj.insert ("time",get_cot_dateTime(query1.value(1).toString()).toString (Qt::DefaultLocaleLongDate));
                    jsarr.append (jsobj);
                }
            }
        }
        else {
            QString time1String = my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").left(2)+"-"+get_cot_date_month(my_ethlene_datas.time1[i-1].date().month())+"-"+my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").mid(6,2)+" "+my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").mid(9,2)+":"+my_ethlene_datas.time1[i-1].toString("dd-MM-yy HH-mm-ss").mid(12,2);
            qDebug()<<"查询检查BBB"<<query1.exec("select name,ts,value from history where name like 'TI16"+QString::number(i, 10)+"_5' and ts >='"+time1String+":00.0' and ts<='"+time1String+":59.0'");


            while(query1.next()){
                if(my_ethlene_datas.time2[i-1].toString()==""||
                        ( my_ethlene_datas.time1[i-1].date()==get_cot_date(query1.value(1).toString())&&
                          my_ethlene_datas.time1[i-1].time().hour()==get_cot_dateTime(query1.value(1).toString()).time().hour()&&
                          my_ethlene_datas.time1[i-1].time().minute()==get_cot_dateTime(query1.value(1).toString()).time().minute()
                          )){
                    my_ethlene_datas.time2[i-1]=get_cot_dateTime(query1.value(1).toString());
                    my_ethlene_datas.cot_temp[i-1]=query1.value(2).toInt();

                    QJsonObject jsobj;
                    jsobj.insert ("temp",query1.value(2).toInt());
                    jsobj.insert ("time",get_cot_dateTime(query1.value(1).toString()).toString (Qt::DefaultLocaleLongDate));
                    jsarr.append (jsobj);
                }
            }
        }
    }


    //断开远程数据库连接
    db.close();

    return jsarr;

}

//使用详细看压缩文件中的说明文档。ODBC配置后，SQL语句访问实时数据库的表名为history，字段名分别为name（位号）、ts（时间）、value（值）。这里的位号就是炉管号，如48号管，位号就是TI1648_5，45号管，位号就是TI1645_5；时间格式是“日-月-年”，且月份用英文缩写；值就是对应炉管的COT值。一个例子：select name,ts,value

//2015 ,08,28诊断与检测函数
void MysqlServer::testDatas(int forunceNum,int tubeNum,QDateTime from_DateTime,QDateTime to_DateTime){
    //清空数据
    tube_in_test_datas.clear();
    tube_out_test_datas.clear();
    tube_cot_test_datas.clear();

//    qDebug()<<"从时间："<<from_DateTime.toString()<<"到时间："<<to_DateTime.toString();
    /**********************************查询本机的数据库**********************************/
    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){

        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }

    //获取数据
    QSqlQuery query;
    //过滤入管的数据语句
    /*qDebug()<<"查询检测"<<*/query.exec("select * from table_in");
    //数据获取
    while(query.next()){
        //判断初始值是否为空
        if(query.value(4).toDateTime().date()>=from_DateTime.date()&&query.value(4).toDateTime().date()<=to_DateTime.date()&&query.value(2).toInt()==tubeNum&&query.value(1).toInt()==forunceNum){
            datas_time data;
            data.time=query.value(4).toDateTime();
            data.temp=query.value(3).toInt();
            tube_in_test_datas.append(data);

        }

    }
    /*********清空查询条件********/
    query.clear();
    /*********清空查询条件********/

    /*qDebug()<<"查询检测"<<*/query.exec("select * from table_out");
    //数据获取
    while(query.next()){
        //判断初始值是否为空
        if(query.value(4).toDateTime().date()>=from_DateTime.date()&&query.value(4).toDateTime().date()<=to_DateTime.date()&&query.value(2)==tubeNum&&query.value(1)==forunceNum){
            datas_time data;
            data.time=query.value(4).toDateTime();
            data.temp=query.value(3).toInt();
            tube_out_test_datas.append(data);

        }

    }
    //关闭数据库
    db.close();
    /**********************************查询本机的数据库**********************************/

    //处理数据
//    qDebug()<<"数据处理****************：“";
    mdeal_with(&tube_in_test_datas);
    mdeal_with(&tube_out_test_datas);

    /**********************************查询石油那边的数据库**********************************/
//    //创建石油厂数据接口
//    db=QSqlDatabase::addDatabase("QODBC");

//    //连接石油厂数据库
//    //
//    db.setHostName("10.112.200.22");
//    db.setDatabaseName("History");
//    db.setPort(10014);


//    if(db.open()){

//        qDebug()<<"remote database is established!";
//    }
//    else{
//        qDebug()<<"faled to connect to remote database";
//        return;
//    }




    //通过链接石油厂数据库加载COT温度数据

//    QSqlQuery query1;
//    query1.setForwardOnly(true);
//    if(tubeNum<10){
//        QString time1String = QString::number(from_DateTime.date().day(), 10)+"-"+get_cot_date_month(from_DateTime.date().month())+"-"+QString::number(from_DateTime.date().year(), 10).right(2);
//        QString time2String = QString::number(to_DateTime.date().day(), 10)+"-"+get_cot_date_month(to_DateTime.date().month())+"-"+QString::number(to_DateTime.date().year(), 10).right(2);
//        query1.exec("select name,ts,value from history where name like 'TI160"+QString::number(tubeNum, 10)+"_5' and ts >='"+time1String+" 00:00:00.0' and ts<='"+time2String+" 23:59:59.0'");
//        while(query1.next()){
//            while(query1.next()){
//                if(get_cot_dateTime(query1.value(1).toString())>=from_DateTime&&get_cot_dateTime(query1.value(1).toString())<=to_DateTime&&query1.value(0).toString().mid(4,2).toInt()==tubeNum&&query1.value(0).toString().right(1).toInt()==forunceNum){
//                    datas_time data;
//                    data.time=get_cot_dateTime(query1.value(1).toString());
//                    data.temp=query1.value(2).toInt();
//                    //                /*tube_cot_single_s_datas*/.append(data);
//                    tube_cot_show_datas.append(data);

//                }
//            }
//        }
//    }
//    else {
//        QString time1String = QString::number(from_DateTime.date().day(), 10)+"-"+get_cot_date_month(from_DateTime.date().month())+"-"+QString::number(from_DateTime.date().year(), 10).right(2);
//        QString time2String = QString::number(to_DateTime.date().day(), 10)+"-"+get_cot_date_month(to_DateTime.date().month())+"-"+QString::number(to_DateTime.date().year(), 10).right(2);
//        query1.exec("select name,ts,value from history where name like 'TI16"+QString::number(tubeNum, 10)+"_5' and ts >='"+time1String+" 00:00:00.0' and ts<='"+time2String+" 23:59:59.0'");
//        while(query1.next()){
//            while(query1.next()){
//                if(get_cot_dateTime(query1.value(1).toString())>=from_DateTime&&get_cot_dateTime(query1.value(1).toString())<=to_DateTime&&query1.value(0).toString().mid(4,2).toInt()==tubeNum&&query1.value(0).toString().right(1).toInt()==forunceNum){
//                    datas_time data;
//                    data.time=get_cot_dateTime(query1.value(1).toString());
//                    data.temp=query1.value(2).toInt();
//                    tube_cot_show_datas.append(data);

//                }
//            }
//        }
//        //断开远程数据库连接
//        db.close();
//        /**********************************查询石油那边的数据库**********************************/

//        //处理数据
//        qDebug()<<"数据处理****************：“";
//        mdeal_with(&tube_cot_show_datas);

//    }

}

//应用包含工作簿包含sheet
void MysqlServer::exportExcel1()      //手动导出excel
{
    QString creatPath=QFileDialog::getSaveFileName(0,tr("Export Excel"),"./aaa.xls",tr("Microsoft Office 2003 (*.xls)"));//获取保存路径
    if(!creatPath.isEmpty()){
        excel = new QAxObject(this);
        excel->setControl("Excel.Application");
        excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
        excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
        workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
        workbooks->dynamicCall("Add");//新建一个工作簿
        workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
        worksheets = workbook->querySubObject("Sheets");//获取工作表集合
        worksheets->querySubObject("Add()");//论带不带括号的差别??
        QAxObject * a = worksheets->querySubObject("Item(int)", 1);
        a->setProperty("Name","exportExcel");

//        qDebug()<<"add sheet";

        worksheet = worksheets->querySubObject("Item(const QString&)", "exportExcel");//获取工作表集合的工作表名

        //写表头
        QAxObject *header;
        QString  headerStr;
        for ( int j = 1 ; j < 49 ; j++)
        {
            header = worksheet->querySubObject("Cells(int,int)", 1,5*j-4);//获取单元格
            headerStr = "第"+ QString::number(j) + "号管";
            header->dynamicCall("Value", headerStr);//设置单元格的值
            header = header->querySubObject("Font");//这句的作用应该是获取字体对象??
            header->setProperty("Bold", true);//设置黑体
        }
        //最终为excell的列索引
        QStringList AtoZ;
        AtoZ<<""<<"A"<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H"<<"I"<<"J"<<"K"<<"L"<<"M"<<"N"<<"O"<<"P"<<"Q"<<"R"<<"S"<<"T"<<"U"<<"V"<<"W"<<"X"<<"Y"<<"Z";
        QStringList AAZZ;
        AAZZ<<""<<"A"<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H"<<"I"<<"J"<<"K"<<"L"<<"M"<<"N"<<"O"<<"P"<<"Q"<<"R"<<"S"<<"T"<<"U"<<"V"<<"W"<<"X"<<"Y"<<"Z";
        for ( int i = 1 ; i < 27 ; i++)
        {
            for(int j = 1 ; j <27 ;j++)
            {
                AAZZ.append(AtoZ.at(i)+AtoZ.at(j));
            }
        }
        //左右居中 合并单元格
        for ( int j = 1 ; j < 241 ; j++)
        {
            QString cell;
            cell.append(AAZZ.at(j));
            cell.append(QString::number(1));
            cell.append(":");
            cell.append(AAZZ.at(j+4));
            cell.append(QString::number(1));
            qDebug()<<"合并字符:"<<cell;
            QAxObject *range = worksheet->querySubObject("Range(const QString&)", cell);//获取范围对象
            //                range->setProperty("VerticalAlignment", -4108);//xlCenter上下居中
            range->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中-4108是居中对齐的参数值
            range->setProperty("WrapText", true);
            range->setProperty("MergeCells", true);
            j = j+4;
        }
        //表头
        for ( int j = 1 ; j < 49 ; j++)
        {
            header = worksheet->querySubObject("Cells(int,int)", 2,5*j-4);//获取单元格
            headerStr = "入管时间";
            header->dynamicCall("Value", headerStr);//设置单元格的值
            header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            header = header->querySubObject("Font");
            header->setProperty("Bold", true);//设置黑体
            QString columnsStr;
            columnsStr = AAZZ.at(5*j-4) + ":"+AAZZ.at(5*j-4);
            header= worksheet->querySubObject("Columns(const QString&)",columnsStr );//为了列宽
            header->setProperty("ColumnWidth", 27);//设置列宽

            header = worksheet->querySubObject("Cells(int,int)", 2,5*j-3);//获取单元格
            headerStr = "入管温度";
            header->dynamicCall("Value", headerStr);//设置单元格的值
            header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            header = header->querySubObject("Font");
            header->setProperty("Bold", true);//设置黑体

            header = worksheet->querySubObject("Cells(int,int)", 2,5*j-2);//获取单元格
            headerStr = "出管时间";
            header->dynamicCall("Value", headerStr);//设置单元格的值
            header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            header = header->querySubObject("Font");
            header->setProperty("Bold", true);//设置黑体
            columnsStr = AAZZ.at(5*j-2) + ":"+AAZZ.at(5*j-2);
            header= worksheet->querySubObject("Columns(const QString&)",columnsStr );
            header->setProperty("ColumnWidth", 27);//设置列宽

            header = worksheet->querySubObject("Cells(int,int)", 2,5*j-1);//获取单元格
            headerStr = "出管温度";
            header->dynamicCall("Value", headerStr);//设置单元格的值
            header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            header = header->querySubObject("Font");
            header->setProperty("Bold", true);//设置黑体

            header = worksheet->querySubObject("Cells(int,int)", 2,5*j-0);//获取单元格
            headerStr = "COT温度";
            header->dynamicCall("Value", headerStr);//设置单元格的值
            header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            header = header->querySubObject("Font");
            header->setProperty("Bold", true);//设置黑体
        }
        //左右居中??没用
        for ( int j = 1 ; j < 241 ; j++)
        {
            header = worksheet->querySubObject("Cells(int,int)", 3,j);//获取单元格
            header->dynamicCall("Value", QString::number(j));//设置单元格的值
            header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
        }



        //写数据
        QAxObject *datas;
        QString  datasStr;
//        for ( int i  = 1 ; i < 241 ; i++ ){
//            for ( int j = 1 ; j <10 ; j++){
//                datasStr = "( " + QString::number(i) + " ,  " + QString::number(j) + " ) ";
//                datas = worksheet->querySubObject("Cells(int,int)", i+2,j);//获取单元格
//                datas->dynamicCall("Value", datasStr);//设置单元格的值
//                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
//            }
//        }


        for ( int i  = 1 ; i < 49 ; i++ )
        {
            //入管时间
            for ( int j = 0 ; j < get_all_tube_in_count(i) ; j++)
            {
                datasStr = tube_in_full_search_datas[i-1].at(j).time.toString(Qt::ISODate);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-4);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //入管温度
            for ( int j = 0 ; j < get_all_tube_in_count(i) ; j++)
            {
                datasStr = QString::number(tube_in_full_search_datas[i-1].at(j).temp);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-3);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //出管时间
            for ( int j = 0 ; j < get_all_tube_in_count(i) ; j++)
            {
                datasStr = tube_out_full_search_datas[i-1].at(j).time.toString(Qt::ISODate);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-2);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //出管温度
            for ( int j = 0 ; j < get_all_tube_out_count(i) ; j++)
            {
                datasStr = QString::number(tube_out_full_search_datas[i-1].at(j).temp);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-1);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //COT温度
            for ( int j = 0 ; j < get_all_tube_in_count(i) ; j++)
            {
//                datasStr = QString::number(tube_cot_full_search_datas[i-1].at(j).temp);
                datasStr = "COTtemp";
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
        }

//        qDebug()<<"write datas";

        //保存并关闭
        workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(creatPath));//保存至creatPath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
        workbook->dynamicCall("Close()");//关闭工作簿
//        qDebug()<<"export excel successed";
    }

}

void MysqlServer::exportExcel2()      //新建excel
{




    //创建auto_excel文件夹
    QDir *temp = new QDir;
    bool exist = temp->exists("D://Desktop//auto_excel");
    if(exist)
        qDebug()<<"the file exist";
    else
    {
        bool ok = temp->mkdir("D://Desktop//auto_excel");
        if( ok )
            qDebug()<<"creat the file";
    }


    QString dataYear = QString::number(QDate::currentDate().year());
    QString creatPath = "D:/Desktop/auto_excel/"+dataYear+".xls";
    QDir *excelFile = new QDir;
    bool excelExist = excelFile->exists(creatPath);
    if(excelExist){
        qDebug()<<"the excel exist";
    }
    else{
        qDebug()<<"creating excel";
        //创建excel文件
        excel = new QAxObject(this);
        excel->setControl("Excel.Application");
        excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
        excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
        workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
        workbooks->dynamicCall("Add");//新建一个工作簿
        workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
        worksheets = workbook->querySubObject("Sheets");//获取工作表集合

        //添加工作表
        QStringList months;
        months << "十二月" << "十一月" << "十月" << "九月"<<"八月"<<"七月"<<"六月"<<"五月"<<"四月"<<"三月"<<"二月"<<"一月";
        for (int i=0;i<12;i++)
        {
            worksheets->querySubObject("Add()");
            QAxObject * a = worksheets->querySubObject("Item(int)", 1);
            a->setProperty("Name",months.at(i));
        }

        //删除sheet1
        QAxObject * del = worksheets->querySubObject("Item(const QString&)", "sheet1");
        del->dynamicCall("delete");


        //写表头
        for ( int i = 0; i < 12 ; i++)
        {
            worksheet = worksheets->querySubObject("Item(const QString&)",months.at(i));//获取工作表集合的工作表名
            //写表头
            QAxObject *header;
            QString  headerStr;
            for ( int j = 1 ; j < 49 ; j++)
            {
                header = worksheet->querySubObject("Cells(int,int)", 1,5*j-4);//获取单元格
                headerStr = "第"+ QString::number(j) + "号管";
                header->dynamicCall("Value", headerStr);//设置单元格的值
                header = header->querySubObject("Font");
                header->setProperty("Bold", true);//设置黑体
            }
            QStringList AtoZ;
            AtoZ<<""<<"A"<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H"<<"I"<<"J"<<"K"<<"L"<<"M"<<"N"<<"O"<<"P"<<"Q"<<"R"<<"S"<<"T"<<"U"<<"V"<<"W"<<"X"<<"Y"<<"Z";
            QStringList AAZZ;
            AAZZ<<""<<"A"<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H"<<"I"<<"J"<<"K"<<"L"<<"M"<<"N"<<"O"<<"P"<<"Q"<<"R"<<"S"<<"T"<<"U"<<"V"<<"W"<<"X"<<"Y"<<"Z";
            for ( int i = 1 ; i < 27 ; i++)
            {
                for(int j = 1 ; j <27 ;j++)
                {
                    AAZZ.append(AtoZ.at(i)+AtoZ.at(j));
                }
            }
            //左右居中 合并单元格
            for ( int j = 1 ; j < 241 ; j++)
            {
                QString cell;
                cell.append(AAZZ.at(j));
                cell.append(QString::number(1));
                cell.append(":");
                cell.append(AAZZ.at(j+4));
                cell.append(QString::number(1));
                QAxObject *range = worksheet->querySubObject("Range(const QString&)", cell);
                //                range->setProperty("VerticalAlignment", -4108);//xlCenter上下居中
                range->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
                range->setProperty("WrapText", true);
                range->setProperty("MergeCells", true);
                j = j+4;
            }
            //表头
            for ( int j = 1 ; j < 49 ; j++)
            {
                header = worksheet->querySubObject("Cells(int,int)", 2,5*j-4);//获取单元格
                headerStr = "入管时间";
                header->dynamicCall("Value", headerStr);//设置单元格的值
                header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
                header = header->querySubObject("Font");
                header->setProperty("Bold", true);//设置黑体
                QString columnsStr;
                columnsStr = AAZZ.at(5*j-4) + ":"+AAZZ.at(5*j-4);
                header= worksheet->querySubObject("Columns(const QString&)",columnsStr );
                header->setProperty("ColumnWidth", 27);//设置列宽

                header = worksheet->querySubObject("Cells(int,int)", 2,5*j-3);//获取单元格
                headerStr = "入管温度";
                header->dynamicCall("Value", headerStr);//设置单元格的值
                header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
                header = header->querySubObject("Font");
                header->setProperty("Bold", true);//设置黑体

                header = worksheet->querySubObject("Cells(int,int)", 2,5*j-2);//获取单元格
                headerStr = "出管时间";
                header->dynamicCall("Value", headerStr);//设置单元格的值
                header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
                header = header->querySubObject("Font");
                header->setProperty("Bold", true);//设置黑体
                columnsStr = AAZZ.at(5*j-2) + ":"+AAZZ.at(5*j-2);
                header= worksheet->querySubObject("Columns(const QString&)",columnsStr );
                header->setProperty("ColumnWidth", 27);//设置列宽

                header = worksheet->querySubObject("Cells(int,int)", 2,5*j-1);//获取单元格
                headerStr = "出管温度";
                header->dynamicCall("Value", headerStr);//设置单元格的值
                header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
                header = header->querySubObject("Font");
                header->setProperty("Bold", true);//设置黑体

                header = worksheet->querySubObject("Cells(int,int)", 2,5*j-0);//获取单元格
                headerStr = "COT温度";
                header->dynamicCall("Value", headerStr);//设置单元格的值
                header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
                header = header->querySubObject("Font");
                header->setProperty("Bold", true);//设置黑体
            }
            //左右居中
            for ( int j = 1 ; j < 241 ; j++)
            {
                header = worksheet->querySubObject("Cells(int,int)", 3,j);//获取单元格
                header->dynamicCall("Value", QString::number(j));//设置单元格的值
                header->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }


        }

        //保存冰关闭
        workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(creatPath));//保存至creatPath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
        workbook->dynamicCall("Close()");//关闭工作簿
        excelExist = true;
//        qDebug()<<"excel has been created";
    }

}

void MysqlServer::exportExcel3()      //自动写入excel
{
//    qDebug()<<"exportExcel3()  ";
    //在线程中初始化com
    HRESULT r = OleInitialize(0);
        if (r != S_OK && r != S_FALSE)
        {
            qWarning("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
        }

    QDateTime nowDateTime;
    nowDateTime = QDateTime::currentDateTime();
    nowDateTime.addMonths(-1);
    int month = nowDateTime.date().month();

    QStringList months;
    months << "一月" << "二月" << "三月" << "四月"<<"五月"<<"六月"<<"七月"<<"八月"<<"九月"<<"十月"<<"十一月"<<"十二月";

    QString creatPath = "D:/Desktop/auto_excel/2015.xls";
    //excel已经存在 写入数据
    if(false){
        excel = new QAxObject(this);
        excel->setControl("Excel.Application");//连接Excel控件
//        qDebug()<<"connect Excel.exe ";
        excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
        excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
        workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
        workbooks->dynamicCall("Open (const QString&)", QString(creatPath));//打开已建好的excel
//        qDebug()<<"open the excel";
        workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
        worksheets = workbook->querySubObject("Sheets");//获取工作表集合
        worksheet = worksheets->querySubObject("Item(const QString&)",months.at(month-1));//获取工作表集合的工作表名

        //写数据
        QAxObject *datas;
        QString  datasStr;
//        for ( int i  = 1 ; i < 100 ; i++ ){
//            for ( int j = 1 ; j <100 ; j++){
//                datas = worksheet->querySubObject("Cells(int,int)", 2,5*j-4);//获取单元格
//                datas->dynamicCall("Value", datasStr);//设置单元格的值
//                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
//            }
//        }

        for ( int i  = 1 ; i < 49 ; i++ )
        {
            //入管时间
            for ( int j = 0 ; j < get_all_tube_in_count(1) ; j++)
            {
                datasStr = tube_in_full_search_datas[i-1].at(j).time.toString(Qt::ISODate);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-4);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //入管温度
            for ( int j = 0 ; j < get_all_tube_in_count(1) ; j++)
            {
                datasStr = QString::number(tube_in_full_search_datas[i-1].at(j).temp);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-3);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //出管时间
            for ( int j = 0 ; j < get_all_tube_in_count(1) ; j++)
            {
                datasStr = tube_out_full_search_datas[i-1].at(j).time.toString(Qt::ISODate);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-2);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //出管温度
            for ( int j = 0 ; j < get_all_tube_in_count(1) ; j++)
            {
                datasStr = QString::number(tube_out_full_search_datas[i-1].at(j).temp);
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5-1);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
            //COT温度
            for ( int j = 0 ; j < get_all_tube_in_count(1) ; j++)
            {
//                datasStr = QString::number(tube_cot_full_search_datas[i-1].at(j).temp);
                datasStr = "COTtemp";
                datas = worksheet->querySubObject("Cells(int,int)", j+3,i*5);//获取单元格
                datas->dynamicCall("Value", datasStr);//设置单元格的值
                datas->setProperty("HorizontalAlignment", -4108);//xlCenter左右居中
            }
        }

//        qDebug()<<"write datas";

        workbook->dynamicCall("Save()");//保存工作簿
        workbook->dynamicCall("Close()");//关闭工作簿

//        qDebug()<<"save the excel";

        excel->dynamicCall("Quit()");//关闭excel
        //        excel=NULL;
    }
    //    5.释放Excel
    /*****************************************
            void  freeExcel()
            {
                if (pApplication != NULL)
                {
                    pApplication->dynamicCall("Quit()");
                    delete pApplication;
                    pApplication = NULL;
                }
            }
            *******************************************/



        OleUninitialize();
}

/*********************初始化警戒温度函数*****************************/
void MysqlServer::init_aler_temps(){
    //创建
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("schema1");
    db.setUserName("root");
    db.setPassword("");

    //链接数据库
    if(db.open()){

        qDebug()<<"database is established!";
    }
    else{
        qDebug()<<"faled to connect to database";
    }

    //获取入管诊断数据
    QSqlQuery query;
    qDebug()<<"查询检测"<<query.exec("select * from aler_temps ");

    //数据获取
    while(query.next()){
        aler_temps[0]=query.value(1).toInt();
        aler_temps[1]=query.value(2).toInt();
        aler_temps[2]=query.value(3).toInt();
        aler_temps[3]=query.value(4).toInt();
        qDebug()<<"入管警戒温度"<<query.value(1).toInt();
        qDebug()<<"出警戒温度"<<query.value(2).toInt();
        qDebug()<<"COT警戒温度"<<query.value(3).toInt();
        qDebug()<<"管间警戒温度差"<<query.value(4).toInt();
    }
    db.close();
}

